def query_from_teilehis(db_engine, from_time_str, to_time_str):
    # sql = "SELECT prozessid, rtn, COUNT(*) cnt FROM (SELECT dmcode, rtn, prozessid, azeit, akz, statusid, RANK() OVER (PARTITION BY dmcode,prozessid ORDER BY zeitpunkt DESC) rank FROM Als_rc.teilehist WHERE prozessid IN (200,210,500,558,270,570,526,581,528,601) AND rtn IN(8615214,8615218,8616102,8616126,8666898,8667034,8667036) AND zeitpunkt >=TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND zeitpunkt < TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') ) GROUP BY prozessid, rtn ORDER BY prozessid, rtn" % (
    #     from_time_str, to_time_str)
    sql = "SELECT prozessid, rtn, COUNT(*) cnt FROM (SELECT dmcode, rtn, prozessid, azeit, akz, statusid, RANK() OVER (PARTITION BY dmcode,prozessid ORDER BY prozessid,zeitpunkt DESC) rang FROM Als_rc.teilehist WHERE zeitpunkt >=TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND zeitpunkt <TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND SUBSTR(dmcode,1,1)='2' AND rtn IN ('8615214','8615218','8616102','8616126') AND azeit IS NOT NULL ) WHERE rang=1 GROUP BY prozessid, rtn ORDER BY prozessid, rtn" % (
        from_time_str, to_time_str)
    return db_engine.execute(sql)


def build_teilehis_data(db_engine, from_time_str, to_time_str):
    data = query_from_teilehis(db_engine, from_time_str, to_time_str)
    # prozessid_array = [200, 210, 500, 558, 270, 570, 526, 581, 528, 601]
    # rtn_array = [8615214, 8615218, 8616102, 8616126, 8666898, 8667034, 8667036]
    teile_dict = {}
    for item in data:
        process_id_str = str(item['prozessid'])
        if process_id_str == '240' or process_id_str == '242':
            idx = '240242' + '_' + str(item['rtn'])
            if idx in teile_dict:
                teile_dict[idx] += item['cnt']
            else:
                teile_dict[idx] = item['cnt']
        else:
            idx = process_id_str + '_' + str(item['rtn'])
            teile_dict[idx] = item['cnt']

    # for prozessid in prozessid_array:
    #     for rtn in rtn_array:

    return teile_dict
